BEGIN;
/* ---------------------------------------------------------------------- */
/* Script generated with: DeZign for Databases v6.0.0                     */
/* Target DBMS:           PostgreSQL 8.3                                  */
/* Project file:          win_observations.dez                            */
/* Project name:                                                          */
/* Author:                                                                */
/* Script type:           Database creation script                        */
/* Created on:            2010-01-12 17:02                                */
/* ---------------------------------------------------------------------- */


/* ---------------------------------------------------------------------- */
/* Tables                                                                 */
/* ---------------------------------------------------------------------- */

/* ---------------------------------------------------------------------- */
/* Add table "partofday"                                                  */
/* ---------------------------------------------------------------------- */

CREATE TABLE partofday (
    id SERIAL  NOT NULL,
    label CHARACTER VARYING,
    CONSTRAINT PKey_tod PRIMARY KEY (id)
)
 WITH (OIDS=FALSE);

/* ---------------------------------------------------------------------- */
/* Add table "win_observations"                                           */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_observations (
    id SERIAL  NOT NULL,
    s_taxa CHARACTER VARYING,
    s_kingdom CHARACTER VARYING,
    s_phylum CHARACTER VARYING,
    s_class CHARACTER VARYING,
    s_order CHARACTER VARYING,
    s_family CHARACTER VARYING,
    s_genus CHARACTER VARYING,
    s_species CHARACTER VARYING,
    s_scientific_name CHARACTER VARYING,
    gender CHARACTER VARYING,
    lifestage CHARACTER VARYING,
    col2009ac_species_id INTEGER,
    num_individuals INTEGER DEFAULT 1,
    is_date_range INTEGER DEFAULT 0,
    on_date DATE,
    from_date DATE,
    to_date DATE,
    is_exact_time INTEGER DEFAULT 1,
    at_time TIME,
    altitude INTEGER DEFAULT 0,
    depth INTEGER DEFAULT 0,
    is_waterbody_related INTEGER DEFAULT 0,
    waterbody_type CHARACTER VARYING,
    waterbody_name CHARACTER VARYING,
    observer_id INTEGER  NOT NULL,
    comments CHARACTER VARYING,
    record_language CHARACTER VARYING,
    created_by INTEGER,
    created_date DATE,
    modified_by INTEGER,
    modified_date DATE,
    topology GEOMETRY,
    CONSTRAINT pk_win_observations PRIMARY KEY (id)
);
COMMENT ON COLUMN win_observations.s_taxa IS 'Taxanomy';
COMMENT ON COLUMN win_observations.s_kingdom IS 'Kingdom';
COMMENT ON COLUMN win_observations.s_phylum IS 'Phylum';
COMMENT ON COLUMN win_observations.s_class IS 'Class';
COMMENT ON COLUMN win_observations.s_order IS 'Order';
COMMENT ON COLUMN win_observations.s_family IS 'Family';
COMMENT ON COLUMN win_observations.s_genus IS 'Genus';
COMMENT ON COLUMN win_observations.s_species IS 'Species';
COMMENT ON COLUMN win_observations.s_scientific_name IS 'Scientific Name';
COMMENT ON COLUMN win_observations.gender IS 'Gender';
COMMENT ON COLUMN win_observations.lifestage IS 'Life Stage';
COMMENT ON COLUMN win_observations.num_individuals IS 'Number of Individuals';
COMMENT ON COLUMN win_observations.on_date IS 'On Date';
COMMENT ON COLUMN win_observations.from_date IS 'From Date';
COMMENT ON COLUMN win_observations.to_date IS 'To Date';
COMMENT ON COLUMN win_observations.at_time IS 'At Time';
COMMENT ON COLUMN win_observations.altitude IS 'Altitude';
COMMENT ON COLUMN win_observations.depth IS 'Depth';
COMMENT ON COLUMN win_observations.waterbody_type IS 'Waterbody Type';
COMMENT ON COLUMN win_observations.waterbody_name IS 'Waterbody Name';
COMMENT ON COLUMN win_observations.comments IS 'Comments';
COMMENT ON COLUMN win_observations.record_language IS 'Record Language';
COMMENT ON COLUMN win_observations.created_by IS 'Created By';
COMMENT ON COLUMN win_observations.created_date IS 'Created Date';
COMMENT ON COLUMN win_observations.modified_by IS 'Modified By';
COMMENT ON COLUMN win_observations.modified_date IS 'Modified Date';
COMMENT ON COLUMN win_observations.topology IS 'Topology';

/* ---------------------------------------------------------------------- */
/* Add table "win_common_names"                                           */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_common_names (
    id SERIAL  NOT NULL,
    observation_id INTEGER,
    common_name CHARACTER VARYING,
    cn_language CHARACTER VARYING,
    region CHARACTER VARYING,
    CONSTRAINT PK_win_common_names PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_observation_location"                                   */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_observation_location (
    id SERIAL  NOT NULL,
    observation_id INTEGER  NOT NULL,
    country CHARACTER VARYING,
    region CHARACTER VARYING,
    district CHARACTER VARYING,
    city CHARACTER VARYING,
    locality CHARACTER VARYING,
    CONSTRAINT PK_win_observation_location PRIMARY KEY (id)
);
COMMENT ON COLUMN win_observation_location.country IS 'Country';
COMMENT ON COLUMN win_observation_location.region IS 'Region/ State';
COMMENT ON COLUMN win_observation_location.district IS 'District';
COMMENT ON COLUMN win_observation_location.city IS 'City';
COMMENT ON COLUMN win_observation_location.locality IS 'Locality';

/* ---------------------------------------------------------------------- */
/* Add table "win_observations_partofday"                                 */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_observations_partofday (
    id SERIAL  NOT NULL,
    observation_id INTEGER  NOT NULL,
    pod_id INTEGER  NOT NULL,
    CONSTRAINT PK_win_observations_partofday PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_observer"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_observer (
    id SERIAL  NOT NULL,
    fname CHARACTER VARYING,
    lname CHARACTER VARYING,
    affiliation CHARACTER VARYING,
    address CHARACTER VARYING,
    mobile CHARACTER VARYING,
    phoneno CHARACTER VARYING,
    email CHARACTER VARYING,
    CONSTRAINT PK_win_observer PRIMARY KEY (id)
);
COMMENT ON COLUMN win_observer.fname IS 'Observer - Fistname';
COMMENT ON COLUMN win_observer.lname IS 'Observer - Lastname';
COMMENT ON COLUMN win_observer.affiliation IS 'Observer - Affiliation';
COMMENT ON COLUMN win_observer.address IS 'Observer - Address';
COMMENT ON COLUMN win_observer.mobile IS 'Observer - Mobile';
COMMENT ON COLUMN win_observer.phoneno IS 'Observer - Phone Number';
COMMENT ON COLUMN win_observer.email IS 'Observer - Email';

/* ---------------------------------------------------------------------- */
/* Add table "win_ref_topo"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_ref_topo (
    id SERIAL  NOT NULL,
    name CHARACTER VARYING  NOT NULL,
    type CHARACTER VARYING  NOT NULL,
    topology POLYGON,
    CONSTRAINT PK_win_ref_topo PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_validation"                                             */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_validation (
    id SERIAL  NOT NULL,
    observation_id INTEGER  NOT NULL,
    status INTEGER DEFAULT 0  NOT NULL,
    comments CHARACTER VARYING,
    validated_by INTEGER  NOT NULL,
    validated_date DATE DEFAULT now(),
    CONSTRAINT PK_win_validation PRIMARY KEY (id)
);

CREATE TABLE win_validator_expertise
(
  id SERIAL NOT NULL,
  uid bigint_unsigned,
  expertise_type character varying NOT NULL,
  expertise_mapping character varying NOT NULL,
  CONSTRAINT "PKey" PRIMARY KEY (id),
  CONSTRAINT "FKey_users" FOREIGN KEY (uid)
      REFERENCES drupal_users (uid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE win_validators
(
  id SERIAL NOT NULL,
  uid bigint_unsigned,
  status smallint_unsigned DEFAULT 0,
  comments character varying,
  reviewed_by smallint_unsigned,
  reviewed_on date,
  CONSTRAINT "PK_win_validators" PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_scientific_name"                                        */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_scientific_name (
    id SERIAL  NOT NULL,
    observation_id INTEGER  NOT NULL,
    name CHARACTER VARYING,
    author CHARACTER VARYING,
    year INTEGER,
    CONSTRAINT PK_win_scientific_name PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_synonyms"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_synonyms (
    id SERIAL  NOT NULL,
    observation_id INTEGER,
    name CHARACTER VARYING,
    author CHARACTER VARYING,
    year INTEGER,
    reason INTEGER,
    CONSTRAINT PK_win_synonyms PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_statuses"                                               */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_statuses (
    id SERIAL  NOT NULL,
    status CHARACTER VARYING,
    CONSTRAINT PK_win_statuses PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Add table "win_observation_images"                                     */
/* ---------------------------------------------------------------------- */

CREATE TABLE win_observation_images (
    id SERIAL  NOT NULL,
    observation_id INTEGER  NOT NULL,
    orig_image CHARACTER varying,
    wm_image CHARACTER varying,
    tn_image CHARACTER varying,
    copyright_text CHARACTER varying,
    CONSTRAINT PK_win_observation_images PRIMARY KEY (id)
);

/* ---------------------------------------------------------------------- */
/* Foreign key constraints                                                */
/* ---------------------------------------------------------------------- */

ALTER TABLE win_observations ADD CONSTRAINT win_observer_win_observations
    FOREIGN KEY (observer_id) REFERENCES win_observer (id);

ALTER TABLE win_common_names ADD CONSTRAINT win_observations_win_common_names
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

ALTER TABLE win_observation_location ADD CONSTRAINT win_observations_win_observation_location
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

ALTER TABLE win_observations_partofday ADD CONSTRAINT partofday_win_observations_partofday
    FOREIGN KEY (pod_id) REFERENCES partofday (id);

ALTER TABLE win_observations_partofday ADD CONSTRAINT win_observations_win_observations_partofday
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

ALTER TABLE win_validation ADD CONSTRAINT win_observations_win_validation
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

ALTER TABLE win_scientific_name ADD CONSTRAINT win_observations_win_scientific_name
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

ALTER TABLE win_synonyms ADD CONSTRAINT win_statuses_win_synonyms
    FOREIGN KEY (reason) REFERENCES win_statuses (id);

ALTER TABLE win_synonyms ADD CONSTRAINT win_observations_win_synonyms
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

ALTER TABLE win_observation_images ADD CONSTRAINT win_observations_win_observation_images
    FOREIGN KEY (observation_id) REFERENCES win_observations (id);

-- View: win_observation_textual_data_view

-- DROP VIEW win_observation_textual_data_view;

CREATE OR REPLACE VIEW win_observation_textual_data_view AS
 SELECT win_observations.id, win_observations.s_kingdom, win_observations.s_phylum, win_observations.s_class, win_observations.s_order, win_observations.s_family, win_observations.s_genus, win_observations.s_species, win_observations.s_scientific_name, win_observations.gender, win_observations.lifestage, win_observations.waterbody_type, win_observations.waterbody_name, win_observations.comments, win_observations.record_language, win_observer.fname, win_observer.lname, win_observer.affiliation, win_observer.address, win_observer.email, win_observation_location.country, win_observation_location.region, win_observation_location.district, win_observation_location.city, win_observation_location.locality
   FROM win_observations, win_observer, win_observation_location
  WHERE win_observations.observer_id = win_observer.id AND win_observations.id = win_observation_location.observation_id
  ORDER BY win_observations.id;

-- View: win_observations_schema_info_view

-- DROP VIEW win_observations_schema_info_view;

CREATE OR REPLACE VIEW win_observations_schema_info_view AS
 SELECT isc.table_name, isc.column_name, isc.is_nullable, isc.data_type, isc.character_maximum_length, isc.numeric_precision, isc.numeric_precision_radix, isc.udt_name, pgd.description
   FROM information_schema.columns isc
   LEFT JOIN pg_class pgc ON pgc.relname = isc.table_name::name
   LEFT JOIN pg_description pgd ON pgc.oid = pgd.objoid AND pgd.objsubid = isc.ordinal_position::integer
  WHERE isc.table_schema::text = 'public'::text;

COMMIT;